--POUR CONTROLE

/*remise dans le bon sens de masque-var*/
--select * from masque_var_trans order by id, COL05
DROP TABLE masque_var_trans ;

CREATE TABLE masque_var_trans AS (
SELECT  ID,lig,
        max(Decode(RANGMULTI2,0,lig01,null)) col01,
        max(Decode(RANGMULTI2,1,lig01,null)) col02,
        max(Decode(RANGMULTI2,2,lig01,null)) col03,
        max(Decode(RANGMULTI2,3,lig01,null)) col04,
        max(Decode(RANGMULTI2,4,lig01,null)) col05,
        max(Decode(RANGMULTI2,5,lig01,null)) col06,
        max(Decode(RANGMULTI2,6,lig01,null)) col07,
        max(Decode(RANGMULTI2,7,lig01,null)) col08,
        max(Decode(RANGMULTI2,8,lig01,null)) col09,
        max(Decode(RANGMULTI2,9,lig01,null)) col10,
        max(Decode(RANGMULTI2,10,lig01,null)) col11,
        max(Decode(RANGMULTI2,11,lig01,null)) col12,
        max(Decode(RANGMULTI2,12,lig01,null)) col13,
        max(Decode(RANGMULTI2,13,lig01,null)) col14,
        max(Decode(RANGMULTI2,14,lig01,null)) col15,
        max(Decode(RANGMULTI2,15,lig01,null)) col16,
        max(Decode(RANGMULTI2,16,lig01,null)) col17,
        max(Decode(RANGMULTI2,17,lig01,null)) col18,
        max(Decode(RANGMULTI2,18,lig01,null)) col19,
        max(Decode(RANGMULTI2,19,lig01,null)) col20,
        max(Decode(RANGMULTI2,20,lig01,null)) col21,
        max(Decode(RANGMULTI2,21,lig01,null)) col22,
        max(Decode(RANGMULTI2,22,lig01,null)) col23,
        max(Decode(RANGMULTI2,23,lig01,null)) col24,
        max(Decode(RANGMULTI2,24,lig01,null)) col25,
        max(Decode(RANGMULTI2,25,lig01,null)) col26,
        max(Decode(RANGMULTI2,26,lig01,null)) col27,
        max(Decode(RANGMULTI2,27,lig01,null)) col28,
        max(Decode(RANGMULTI2,28,lig01,null)) col29,
        max(Decode(RANGMULTI2,29,lig01,null)) col30,
        max(Decode(RANGMULTI2,30,lig01,null)) col31,
        max(Decode(RANGMULTI2,31,lig01,null)) col32,
        max(Decode(RANGMULTI2,32,lig01,null)) col33,
        max(Decode(RANGMULTI2,33,lig01,null)) col34,
        max(Decode(RANGMULTI2,34,lig01,null)) col35,
        max(Decode(RANGMULTI2,35,lig01,null)) col36,
        max(Decode(RANGMULTI2,36,lig01,null)) col37,
        max(Decode(RANGMULTI2,37,lig01,null)) col38,
        max(Decode(RANGMULTI2,38,lig01,null)) col39,
        max(Decode(RANGMULTI2,39,lig01,null)) col40,
        max(Decode(RANGMULTI2,40,lig01,null)) col41,
        max(Decode(RANGMULTI2,41,lig01,null)) col42,
        max(Decode(RANGMULTI2,42,lig01,null)) col43,
        max(Decode(RANGMULTI2,43,lig01,null)) col44,
        max(Decode(RANGMULTI2,44,lig01,null)) col45,
        max(Decode(RANGMULTI2,45,lig01,null)) col46,
        max(Decode(RANGMULTI2,46,lig01,null)) col47,
        max(Decode(RANGMULTI2,47,lig01,null)) col48,
        max(Decode(RANGMULTI2,48,lig01,null)) col49,
        max(Decode(RANGMULTI2,49,lig01,null)) col50,
        max(Decode(RANGMULTI2,50,lig01,null)) col51,
        max(Decode(RANGMULTI2,51,lig01,null)) col52,
        max(Decode(RANGMULTI2,52,lig01,null)) col53,
        max(Decode(RANGMULTI2,53,lig01,null)) col54,
        max(Decode(RANGMULTI2,54,lig01,null)) col55
FROM (
 SELECT 'lig01' lig ,l.* FROM uv_pmasque_var_lig01 l
 UNION ALL
 SELECT 'lig02' lig ,l.* FROM uv_pmasque_var_lig02 l
 UNION ALL
 SELECT 'lig03' lig ,l.* FROM uv_pmasque_var_lig03 l
 UNION ALL
 SELECT 'lig04' lig ,l.* FROM uv_pmasque_var_lig04 l
 UNION ALL
 SELECT 'lig05' lig ,l.* FROM uv_pmasque_var_lig05 l
 UNION ALL
 SELECT 'lig06' lig ,l.* FROM uv_pmasque_var_lig06 l
 UNION ALL
 SELECT 'lig07' lig ,l.* FROM uv_pmasque_var_lig07 l
 UNION ALL
 SELECT 'lig08' lig ,l.* FROM uv_pmasque_var_lig08 l
 UNION ALL
 SELECT 'lig09' lig ,l.* FROM uv_pmasque_var_lig09 l
 UNION ALL
 SELECT 'lig10' lig ,l.* FROM uv_pmasque_var_lig10 l
 UNION ALL
 SELECT 'lig11' lig ,l.* FROM uv_pmasque_var_lig11 l
 UNION ALL
 SELECT 'lig12' lig ,l.* FROM uv_pmasque_var_lig12 l
 UNION ALL
 SELECT 'lig13' lig ,l.* FROM uv_pmasque_var_lig13 l
 UNION ALL
 SELECT 'lig14' lig ,l.* FROM uv_pmasque_var_lig14 l
 UNION ALL
 SELECT 'lig15' lig ,l.* FROM uv_pmasque_var_lig10 l
 UNION ALL
 SELECT 'lig16' lig ,l.* FROM uv_pmasque_var_lig11 l
 UNION ALL
 SELECT 'lig17' lig ,l.* FROM uv_pmasque_var_lig12 l
 UNION ALL
 SELECT 'lig18' lig ,l.* FROM uv_pmasque_var_lig13 l
 UNION ALL
 SELECT 'lig19' lig ,l.* FROM uv_pmasque_var_lig14 l

) ll
GROUP BY id,lig  ) ;


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('VRT_ATT');

 DELETE FROM se_VRT_ATT;


 /*
select * from (
SELECT &outils.stragg(source) source,vrt_cod, att_cod, champsais_cod, recalcul_ok, desvrtlib_ok, presaisie_ok, vrtref_cod, attref_cod, approche_cod, chapref_cod, enreg_id, enreg_s, att_lib, att_lib2, att_rang, attsto_ok, atttaf_ok, attpic_ok, atttrc_ok, desvrt_ok, surcote_val
FROM (
  SELECT 'uv' source ,g.* FROM se_VRT_ATT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.VRT_ATT g
  )

GROUP BY vrt_cod, att_cod, champsais_cod, recalcul_ok, desvrtlib_ok, presaisie_ok, vrtref_cod, attref_cod, approche_cod, chapref_cod, enreg_id, enreg_s, att_lib, att_lib2, att_rang, attsto_ok, atttaf_ok, attpic_ok, atttrc_ok, desvrt_ok, surcote_val
HAVING Count(1)!=2 ) r
ORDER BY vrt_cod, att_cod  */



 INSERT INTO se_VRT_ATT
 (SELECT DISTINCT id vrt_cod,
         col05 att_cod,
         0 champsais_cod,
         'N' recalcul_ok,
         'N' desvrtlib_ok,
         'N' presaisie_ok,
         NULL vrtref_cod,
         NULL attref_cod,
         NULL approche_cod,
         NULL chapref_cod,
         0 enreg_id,
         'N' enreg_s,
         Trim(REPLACE(REPLACE(Nvl(col28,col21),'$A',''),':')) att_lib,
         null att_lib2,
         col05 att_rang,
         Nvl(col40,col33) attsto_ok,
         Nvl(col41,col34) atttaf_ok,
         Nvl(col51,col44) attpic_ok,
         Nvl(col42,col35) atttrc_ok,
         Decode(col40,NULL,col37,col44) desvrt_ok,
         NULL surcote_val

     FROM  masque_var_trans m , se_VRT_VRT v
     WHERE col05 IS NOT NULL AND Length(m.id)<=5
       AND v.vrt_cod(+)=m.id
     );


 UPDATE se_VRT_ATT tf SET enreg_id=Nvl((select enreg_id FROM &erp.vrt_ATT f WHERE f.vrt_cod=tf.vrt_cod AND f.att_cod=tf.att_cod),0) ;
 UPDATE se_VRT_ATT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 UPDATE se_VRT_ATT tf SET CHAMPSAIS_COD=Nvl((select CHAMPSAIS_COD FROM &erp.vrt_ATT f WHERE f.vrt_cod=tf.vrt_cod AND f.att_cod=tf.att_cod),0) ;
 UPDATE se_VRT_ATT tf SET CHAMPSAIS_COD=&outils.gen_CHAMPSAIS.NEXTVAL WHERE CHAMPSAIS_COD IS NULL OR CHAMPSAIS_COD =0;



 commit;





